package kr.hkit.summary_java.erp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.myframework.db.DbUtil;
import kr.hkit.summary_java.erp.dto.CodeTitle;
import kr.hkit.summary_java.erp.dto.Department;

public class CodeDAO implements DaoInterface<CodeTitle> {

	private Connection connection;
	private PreparedStatement pstmt;

	public CodeDAO(Connection connection) {
		this.connection = connection;
	}
	
	@Override
	public void insertItem(CodeTitle item) throws SQLException {
		String sql = "insert into code_title values(?, ?)";// 이 형태의 sql쿼리
		pstmt = connection.prepareStatement(sql);// ?를 쉽게 쓰기 위한 방법
		pstmt.setInt(1, item.getTitleNo());
		pstmt.setString(2, item.getTitleName());
		pstmt.executeUpdate();// 성공하면 1/ 실제 db에 적용하기
		DbUtil.close(pstmt);
	}

	@Override
	public void deleteItembyNo(CodeTitle item) throws SQLException {
		String sql = "delete from code_title where titleno = ?";

		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getTitleNo());
		pstmt.executeUpdate();// 실제 db에 적용하기
		DbUtil.close(pstmt);
	}

	@Override
	public void updateItembyNo(CodeTitle item) throws SQLException {
		String sql = "update code_title set titlename = ? where titleno = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getTitleName());
		pstmt.setInt(2, item.getTitleNo());
		pstmt.executeUpdate();// 실제 db에 적용하기
		DbUtil.close(pstmt);
	}

	@Override
	public ArrayList<CodeTitle> listItems() throws SQLException {
		ArrayList<CodeTitle> list = new ArrayList<>();
		String sql = "select titleno, titlename from code_title";

		pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();// 결과값 포인터
		while (rs.next()) {// 다음 레코드가 있니?
			list.add(new CodeTitle(rs.getInt("titleno"), rs
					.getString("titlename")));
		}

		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}

	@Override
	public CodeTitle searchItem(CodeTitle item) throws SQLException {
		CodeTitle title = null;
		String sql = "select titleno, titlename from code_title where titleno = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getTitleNo());
		ResultSet rs = pstmt.executeQuery();// 결과값 포인터
		if (rs.next()) {
			title = new CodeTitle();
			title.setTitleNo(rs.getInt(1));
			title.setTitleName(rs.getString(2));
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return title;
	}

}
